package cn.pconline.autoclub.task;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * @author chensy
 */
public class DBUtils {

    // 表示定义数据库的用户名
    private static final String USERNAME = FileUtils.getConfig("db.username");
    // 定义数据库的密码
    private static final String PASSWORD = FileUtils.getConfig("db.password");
    // 定义数据库的驱动信息
    private static final String DRIVER = FileUtils.getConfig("db.driver");
    // 定义访问数据库的地址
    private static final String URL = FileUtils.getConfig("db.url");
    private static final Logger log = LoggerFactory.getLogger(DBUtils.class);

    private DBUtils() {
    }

    public static Connection getConnection() {
        try {
            Class.forName(DRIVER);
            return DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (ClassNotFoundException ex) {
            log.error("getConnection fail--ClassNotFoundException", ex);
        } catch (SQLException ex) {
            log.error("getConnection fail--SQLException", ex);
        }
        return null;
    }

    public static boolean getAutoCommitValue(Connection conn) {
        try {
            return conn.getAutoCommit();
        } catch (SQLException ex) {
            log.error("getAutoCommit fail--SQLException", ex);
        }
        throw new RuntimeException("getAutoCommit fail");
    }

    /**
     * 替换分表的表名
     *
     * @param sql
     * @param tableName
     * @param date
     * @return
     */
    public static String getSql(String sql, String tableName, Date date) {
        return sql.replace(tableName, tableName + new SimpleDateFormat("_yyyyMM").format(date));
    }

    public static String getSql(String sql, String tableName, long id) {
        return sql.replace(tableName, tableName + "_" + id % 10);
    }

    public static void releaseConn(Connection connection, PreparedStatement pstmt, ResultSet resultSet) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException ex) {
                log.error("resultSet close fail!", ex);
            }
        }
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException ex) {
                log.error("preparedStatement close fail!", ex);
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException ex) {
                log.error("connection close fail!", ex);
            }
        }
    }

    public static List<String> getColumns(String tableName) throws SQLException {
        String sql = "DESC " + tableName;
        List<String> columns = new ArrayList<String>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            conn = DBUtils.getConnection();
            ps = conn.prepareStatement(sql);
            resultSet = ps.executeQuery();
            if (resultSet != null) {
                while (resultSet.next()) {
                    String column = resultSet.getString(1);
                    log.debug("column: {}", column);
                    columns.add(column);
                }
            }
        } catch (SQLException ex) {
            throw ex;
        } finally {
            DBUtils.releaseConn(conn, ps, resultSet);
        }
        return columns;
    }

    public static List<String> getTables(String tableName, int x) throws SQLException {
        StringBuilder placeholder = new StringBuilder();
        if (x > 0) {
            for (int i = 0; i <= x; i++) {
                placeholder.append("_");
            }
        }
        String sql = String.format("SHOW TABLES LIKE '%s%s'", tableName, placeholder.toString());
        List<String> tables = new ArrayList<String>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            conn = DBUtils.getConnection();
            ps = conn.prepareStatement(sql);
            resultSet = ps.executeQuery();
            if (resultSet != null) {
                while (resultSet.next()) {
                    String table = resultSet.getString(1);
                    log.debug("table: {}", table);
                    tables.add(table);
                }
            }
        } catch (SQLException ex) {
            throw ex;
        } finally {
            DBUtils.releaseConn(conn, ps, resultSet);
        }
        return tables;
    }

    public static String getInsertIgnoreIntoSql(String tablePrefix, long x, List<String> columns) {
        StringBuilder columnBuilder = new StringBuilder();
        StringBuilder paramBuilder = new StringBuilder();
        for (String col : columns) {
            columnBuilder.append(',').append(col);
            paramBuilder.append(",?");
        }
        String table = tablePrefix + x;
        StringBuilder sqlBuilder = new StringBuilder("INSERT IGNORE INTO ");
        sqlBuilder.append(table);
        sqlBuilder.append(" (").append(columnBuilder.substring(1));
        sqlBuilder.append(") values (");
        sqlBuilder.append(paramBuilder.substring(1)).append(")");
        log.debug("getInsertIgnoreIntoSql: {}", sqlBuilder);
        return sqlBuilder.toString();
    }
}
